package com.lgx.common.basic.dao;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.lgx.common.web.Pagination;

/**
 * JDBC查询DAO
 * 
 * @author w10428
 *
 */
public class GeneralJdbcDao extends JdbcDaoSupport {

	/**
	 * JDBC查询
	 * 
	 * @param sql 查询语句
	 * @param clazz 映射实体
	 * @return
	 */
	public List<?> queryJdbcForList(String sql,Class<?> clazz)
	{
		final Class<?> c = clazz;
		List<?> list = this.getJdbcTemplate().query(sql, new RowMapper<Object>(){

			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				Class<?> obj = null;
				Object t = new Object();
				try {
					obj = Class.forName(c.getName());
					t = (Object) obj.newInstance();
					Field[] fields = obj.getDeclaredFields();
					
					ResultSetMetaData rsmd=rs.getMetaData();
					int columnCount = rsmd.getColumnCount();  
					boolean flag = false;
					int count = 1;
					String fieldName = "";
					String fieldType = "";
				    // 输出列名   
				    for (int i=1; i<=columnCount; i++){  
				    	String name = rsmd.getColumnLabel(i);
				    	if(name.contains("_")){
				    		name = name.split("_")[0]+"Id";
				    	}
				    	
				    	for(Field field : fields){
				    		if(name.toLowerCase().equals(field.getName().toLowerCase())){
				    			flag = true;
				    			count = i;
				    			fieldName = field.getName();
				    			fieldType = field.getType().getSimpleName();
				    			break;
				    		}
				    	}
				        
				        if(flag){

							//首字母大写
							String set = "set"+fieldName.substring(0,1).toUpperCase() + fieldName.substring(1); 
							
							Method method = null;

							if("Long".equals(fieldType))
							{
								method = obj.getMethod(set, Long.class);
								if(method != null)
									method.invoke(t, Long.parseLong(rs.getString(count)));
							}else if("long".equals(fieldType))
							{
								method = obj.getMethod(set, long.class);
								if(method != null)
									method.invoke(t, Long.parseLong(rs.getString(count)));
							}else if("Double".equals(fieldType))
							{
								method = obj.getMethod(set, Double.class);
								if(method != null)
									method.invoke(t, rs.getDouble(count));
							}else if("double".equals(fieldType))
							{
								method = obj.getMethod(set, double.class);
								if(method != null)
									method.invoke(t, rs.getDouble(count));
							}else if("int".equals(fieldType))
							{
								method = obj.getMethod(set, int.class);
								if(method != null)
									method.invoke(t, rs.getInt(count));
							}else if("Integer".equals(fieldType))
							{
								method = obj.getMethod(set, Integer.class);
								if(method != null)
									method.invoke(t, rs.getInt(count));
							}else if("Float".equals(fieldType))
							{
								method = obj.getMethod(set, Float.class);
								if(method != null)
									method.invoke(t, rs.getFloat(count));
							}else if("float".equals(fieldType))
							{
								method = obj.getMethod(set, float.class);
								if(method != null)
									method.invoke(t, rs.getFloat(count));
							}else if("Date".equals(fieldType))
							{
								method = obj.getMethod(set, Date.class);
								if(method != null)
									method.invoke(t, rs.getDate(count));
							}else if("String".equals(fieldType))
							{
								method = obj.getMethod(set, String.class);
								if(method != null)
									method.invoke(t, rs.getString(count));
							}
					    }
				    }   
				   
				} catch (ClassNotFoundException e) {
					e.printStackTrace();
				} catch (SecurityException e) {
					e.printStackTrace();
				} catch (NoSuchMethodException e) {
					e.printStackTrace();
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				} catch (InvocationTargetException e) {
					e.printStackTrace();
				} catch (InstantiationException e) {
					e.printStackTrace();
				}
				return t;
			}
			
		});
		
		return list;
	}
	
	/**
	 * 分页
	 * 
	 * @param sql
	 * @param clazz
	 * @param pageSize
	 * @param pageNum
	 * @return
	 */
	public Pagination queryJdbcListPagination(String sql, Class<?> clazz, int pageSize, int pageNum) {
		String counthql = sql.substring(sql.toLowerCase().indexOf("from ")); 
		
        counthql = "select count(*) "+counthql; 
        
		int totalRecordNum = this.getJdbcTemplate().queryForInt(counthql);
		
		pageNum = pageNum -1;
		pageNum = pageNum < 0 ? 0 : pageNum;
		
		String queryhql = sql + " limit " + pageNum*pageSize + " , " + pageSize;
		
		final Class<?> c = clazz;
		List<?> list = this.getJdbcTemplate().query(queryhql, new RowMapper<Object>(){

			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				Class<?> obj = null;
				Object t = new Object();
				try {
					obj = Class.forName(c.getName());
					t = (Object) obj.newInstance();
					Field[] fields = obj.getDeclaredFields();
					
					ResultSetMetaData rsmd=rs.getMetaData();
					int columnCount = rsmd.getColumnCount();  
					boolean flag = false;
					int count = 1;
					String fieldName = "";
					String fieldType = "";
				    // 输出列名   
				    for (int i=1; i<=columnCount; i++){  
				    	String name = rsmd.getColumnLabel(i);
				    	if(name.contains("_")){
				    		name = name.split("_")[0]+"Id";
				    	}
				    	
				    	for(Field field : fields){
				    		if(name.toLowerCase().equals(field.getName().toLowerCase())){
				    			flag = true;
				    			count = i;
				    			fieldName = field.getName();
				    			fieldType = field.getType().getSimpleName();
				    			break;
				    		}
				    	}
				        
				        if(flag){

							//首字母大写
							String set = "set"+fieldName.substring(0,1).toUpperCase() + fieldName.substring(1); 
							
							Method method = null;

							if("Long".equals(fieldType))
							{
								method = obj.getMethod(set, Long.class);
								if(method != null)
									method.invoke(t, Long.parseLong(rs.getString(count)));
							}else if("long".equals(fieldType))
							{
								method = obj.getMethod(set, long.class);
								if(method != null)
									method.invoke(t, Long.parseLong(rs.getString(count)));
							}else if("Double".equals(fieldType))
							{
								method = obj.getMethod(set, Double.class);
								if(method != null)
									method.invoke(t, rs.getDouble(count));
							}else if("double".equals(fieldType))
							{
								method = obj.getMethod(set, double.class);
								if(method != null)
									method.invoke(t, rs.getDouble(count));
							}else if("int".equals(fieldType))
							{
								method = obj.getMethod(set, int.class);
								if(method != null)
									method.invoke(t, rs.getInt(count));
							}else if("Integer".equals(fieldType))
							{
								method = obj.getMethod(set, Integer.class);
								if(method != null)
									method.invoke(t, rs.getInt(count));
							}else if("Float".equals(fieldType))
							{
								method = obj.getMethod(set, Float.class);
								if(method != null)
									method.invoke(t, rs.getFloat(count));
							}else if("float".equals(fieldType))
							{
								method = obj.getMethod(set, float.class);
								if(method != null)
									method.invoke(t, rs.getFloat(count));
							}else if("Date".equals(fieldType))
							{
								method = obj.getMethod(set, Date.class);
								if(method != null)
									method.invoke(t, rs.getDate(count));
							}else if("String".equals(fieldType))
							{
								method = obj.getMethod(set, String.class);
								if(method != null)
									method.invoke(t, rs.getString(count));
							}
					    }
				    }   
				   
				} catch (ClassNotFoundException e) {
					e.printStackTrace();
				} catch (SecurityException e) {
					e.printStackTrace();
				} catch (NoSuchMethodException e) {
					e.printStackTrace();
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				} catch (InvocationTargetException e) {
					e.printStackTrace();
				} catch (InstantiationException e) {
					e.printStackTrace();
				}
				return t;
			}
			
		});
		
		int totalPageNum = totalRecordNum/pageSize;
		if(totalRecordNum%pageSize!=0) {
			totalPageNum+=1;
		}
		Pagination page = new Pagination();
		page.setPageNum(pageNum);
		page.setPageSize(pageSize);
		page.setTotal(totalRecordNum);
		page.setTotalPage(totalPageNum);
		page.setRows(list);
		return page;
	}
}
